﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace SharpDB.DAO
{
    public class MSSqlDAO : DAOBase
    {
        public MSSqlDAO(DbProviderFactory providerFactory, DBType dBType, string connectionString, string paramCharacter, bool checkStruct = true) 
            : base(providerFactory, dBType, connectionString, paramCharacter, checkStruct)
        {
        }

        public override bool ValidateSql(string strSql, out string msg)
        {
            bool bResult = false;
            msg = string.Empty;
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = CreateCmd();
                cmd.Connection = conn;
                conn.Open();
                try
                {
                    cmd.CommandText = "explain plan for " + strSql;
                    cmd.ExecuteNonQuery();
                    bResult = true;
                }
                catch (Exception ex)
                {
                    msg = ex.Message;
                    bResult = false;
                }
            }
            return bResult;
        }

        public override void BulkCopy(DataTable data, string tableName, Dictionary<string, string> columnMappings = null,
           int batchSize = 200000, int bulkCopyTimeout = 60)
        {

            List<string> lstAllColName = null;
            if (CheckStruct)
            {
                lstAllColName = this.Info[tableName];
            }
            SqlBulkCopy bulk = null;
            bulk = new SqlBulkCopy(this.ConnectionString);
            using (bulk)
            {

                if (columnMappings != null)
                {
                    foreach (var colMapping in columnMappings)
                    {
                        if (CheckStruct && !lstAllColName.Contains(colMapping.Value, StringComparer.OrdinalIgnoreCase))
                        {
                            continue;
                        }
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(colMapping.Key, colMapping.Value));
                    }
                }
                else
                {
                    if (CheckStruct)
                    {
                        int colCount = data.Columns.Count;
                        for (int j = 0; j < colCount; j++)
                        {
                            if (!lstAllColName.Contains(data.Columns[j].ColumnName, StringComparer.OrdinalIgnoreCase))
                            {
                                data.Columns.Remove(data.Columns[j].ColumnName);
                            }
                        }
                    }
                }
                bulk.DestinationTableName = tableName;
                bulk.BulkCopyTimeout = bulkCopyTimeout;
                bulk.BatchSize = batchSize;
                bulk.WriteToServer(data);
                //清空，防止内存溢出
                lstAllColName = null;
                data = null;
                #region 自定义处理方式
                //if (data.Rows.Count <= batchSize)
                //{
                //    bulk.WriteToServer(data);
                //}
                //else
                //{
                //    int yushu = data.Rows.Count % batchSize;
                //    int copyCount = 0;
                //    if (yushu == 0)
                //    {
                //        copyCount = data.Rows.Count / batchSize;
                //    }
                //    else
                //    {
                //        copyCount = data.Rows.Count / batchSize + 1;
                //    }

                //    var dRows = data.AsEnumerable();

                //    for (int j = 0; j < copyCount; j++)
                //    {
                //        var drs = dRows.Skip(j * batchSize).Take(batchSize);
                //        bulk.WriteToServer(drs.ToArray());
                //    }
                //} 
                #endregion
            }
        }


        public override void BulkCopy(DbDataReader reader, string tableName, Dictionary<string, string> columnMappings = null,
    int batchSize = 200000, int bulkCopyTimeout = 60)
        {
            List<string> lstAllColName = null;
            if (CheckStruct)
            {
                lstAllColName = this.Info[tableName];
            }
            SqlBulkCopy bulk = null;
            bulk = new SqlBulkCopy(this.ConnectionString);
            using (bulk)
            {
                if (columnMappings != null)
                {
                    foreach (var colMapping in columnMappings)
                    {
                        if (CheckStruct && !lstAllColName.Contains(colMapping.Value, StringComparer.OrdinalIgnoreCase))
                        {
                            continue;
                        }
                        bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(colMapping.Key, colMapping.Value));
                    }
                }
                else
                {
                    if (CheckStruct)
                    {
                        int colCount = reader.FieldCount;
                        for (int j = 0; j < colCount; j++)
                        {
                            if (lstAllColName.Contains(reader.GetName(j), StringComparer.OrdinalIgnoreCase))
                            {
                                bulk.ColumnMappings.Add(new SqlBulkCopyColumnMapping(reader.GetName(j), reader.GetName(j)));
                            }
                        }
                    }
                }
                bulk.DestinationTableName = tableName;
                bulk.BulkCopyTimeout = bulkCopyTimeout;
                bulk.BatchSize = batchSize;
                bulk.WriteToServer(reader);
                //清空，防止内存溢出
                lstAllColName = null;
                reader.Close();
                #region 自定义处理方式
                //if (data.Rows.Count <= batchSize)
                //{
                //    bulk.WriteToServer(data);
                //}
                //else
                //{
                //    int yushu = data.Rows.Count % batchSize;
                //    int copyCount = 0;
                //    if (yushu == 0)
                //    {
                //        copyCount = data.Rows.Count / batchSize;
                //    }
                //    else
                //    {
                //        copyCount = data.Rows.Count / batchSize + 1;
                //    }

                //    var dRows = data.AsEnumerable();

                //    for (int j = 0; j < copyCount; j++)
                //    {
                //        var drs = dRows.Skip(j * batchSize).Take(batchSize);
                //        bulk.WriteToServer(drs.ToArray());
                //    }
                //} 
                #endregion
            }
        }
    }
}
